﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;

namespace DatabaseCore
{
    public class OrderDao : IOrderDao
    {
        public IList<tbl_Order> Search(string orderNo, int orderStatus = 0, string invoiceNo = null, string portOfShipment = null,
            string portOfDestination = null, string poNo = null, string location = null, int role = -1)
        {
            using (var ctx = new ExportDBEntities())
            {
                IQueryable<tbl_Order> orders = from x in ctx.tbl_Order
                                               select x;

                if (!string.IsNullOrEmpty(orderNo))
                    orders = orders.Where(x => x.OrderNo.Contains(orderNo));

                if (!string.IsNullOrEmpty(invoiceNo))
                    orders = orders.Where(x => x.OrderNo.Contains(invoiceNo));

                if (!string.IsNullOrEmpty(portOfShipment))
                    orders = orders.Where(x => x.OrderNo.Contains(portOfShipment));

                if (!string.IsNullOrEmpty(portOfDestination))
                    orders = orders.Where(x => x.OrderNo.Contains(portOfDestination));

                if (!string.IsNullOrEmpty(poNo))
                    orders = orders.Where(x => x.OrderNo.Contains(poNo));

                if (!string.IsNullOrEmpty(location))
                    orders = orders.Where(x => x.OrderNo.Contains(location));

                if (orderStatus != 0)
                    orders = orders.Where(x => x.Status == orderStatus);

                if (role != -1)
                {
                    List<int> statusByRole = ctx.tbl_RoleToOrderStatus.Where(s => s.RoleId == role).Select(status => status.OrderStatusId).ToList();
                    orders = orders.Where(x => statusByRole.Contains(x.Status.Value));

                }

                //orders = orders.Where(x => x.Status == orderStatus);
                //orders = orders.Where(x => orderStatus.Select(c => c & x != 0).Any());
                //orders = orders.Where(x => orderStatus.Contains(x.Status.Value));

                orders = orders.OrderByDescending(x => x.CreateDate);
                return orders.ToList();
            }
        }

        public tbl_Order GetByKey(string orderNo)
        {
            using (var ctx = new ExportDBEntities())
            {
                tbl_Order order = (from x in ctx.tbl_Order
                    where x.OrderNo == orderNo
                    select x).FirstOrDefault();

                return order;
            }
        }

        public string GetRefOrder(string orderNo)
        {
            using (var ctx = new ExportDBEntities())
            {
                var order = (from x in ctx.tbl_Order
                                   where x.OrderNo == orderNo
                                   select x.RefOrderNo).FirstOrDefault();

                return order;
            }
        }

        public bool Insert(tbl_Order entity)
        {
            using (var ctx = new ExportDBEntities())
            {
                ctx.tbl_Order.Add(entity);
                return ctx.SaveChanges() > 0;
            }
        }

        public bool Delete(string  OrderNo)
        {
            using (var ctx = new ExportDBEntities())
            {
                int result = ctx.Database.ExecuteSqlCommand("DELETE FROM ProdQueue WHERE OrderNo = @OrderNo DELETE FROM tbl_OrderDetail WHERE OrderNo = @OrderNo DELETE FROM tbl_Order WHERE OrderNo = @OrderNo", new[] { new SqlParameter("@OrderNo", OrderNo) });
                return result > 0;
            }
        }

        public bool Update(tbl_Order entity)
        {
            using (var ctx = new ExportDBEntities())
            {
                tbl_Order order = (from x in ctx.tbl_Order
                    where x.OrderNo == entity.OrderNo
                    select x).FirstOrDefault();

                if (order == null)
                    return false;

                order.TotalQty = entity.TotalQty;
                order.TotalAmount = entity.TotalAmount;
                order.TotalPackage = entity.TotalPackage;
                order.TotalWeight = entity.TotalWeight;
                order.Status = entity.Status;
                order.UpdateBy = entity.UpdateBy;
                order.UpdateDate = entity.UpdateDate;
                order.Comment = entity.Comment;
                if (entity.SentToApproverTime != null)
                {
                    order.SentToApproverTime = entity.SentToApproverTime;
                }

                if (entity.ApprovedTime != null)
                {
                    order.ApprovedTime = entity.ApprovedTime;
                }

                if (entity.RejectedTime != null)
                {
                    order.RejectedTime = entity.RejectedTime;
                }

                return ctx.SaveChanges() > 0;
            }
        }

        public bool UpdateOrderStatus(string orderNo, int orderStatus, string updateBy)
        {
            using (var ctx = new ExportDBEntities())
            {
                tbl_Order order = (from x in ctx.tbl_Order
                                   where x.OrderNo == orderNo
                                   select x).FirstOrDefault();

                if (order == null)
                    return false;

                order.Status = orderStatus;
                order.UpdateBy = updateBy;
                order.UpdateDate = DateTime.Now;

                return ctx.SaveChanges() > 0;
            }
        }

        public bool CheckOrderIsClose(string orderNo)
        {
            using (var ctx = new ExportDBEntities())
            {
                var result = true;
                var orderDetails = (from x in ctx.tbl_OrderDetail
                                   where x.OrderNo == orderNo && x.IsDeleteFromQueue != true
                                   select x);

                if (orderDetails == null)
                    return true;

                else
                {
                    foreach (var dd in orderDetails)
                    {
                        if ((dd.Qty - (dd.ReceivedQty + (dd.ReviseQty ?? 0)) == 0) && dd.RemainingQty == 0)
                        {
                            result = result & true;
                        }
                        else
                        {
                            result = result & false;
                        }
                    }
                }
                return result;
            }
        }

        public bool UpdateOrderStatus(string orderNo, int orderStatus, string updateBy, bool IsApprove)
        {
            using (var ctx = new ExportDBEntities())
            {
                tbl_Order order = (from x in ctx.tbl_Order
                                   where x.OrderNo == orderNo
                                   select x).FirstOrDefault();

                if (order == null)
                    return false;

                order.Status = orderStatus;
                order.UpdateBy = updateBy;
                order.UpdateDate = DateTime.Now;
                order.ApprovedTime = DateTime.Now;
                return ctx.SaveChanges() > 0;
            }
        }

        public bool UpdateToCloseJob(List<string> orderNo)
        {
            using (var ctx = new ExportDBEntities())
            {
                foreach (var o in orderNo)
                {
                    tbl_Order order = (from x in ctx.tbl_Order
                                       where x.OrderNo == o
                                       select x).FirstOrDefault();

                    if (order != null)
                    {
                        order.Status = 4;
                        order.UpdateBy = "System close job";
                        order.UpdateDate = DateTime.Now;
                    }
                }
                
                return ctx.SaveChanges() > 0;
            }
        }

        public bool UpdateOrderStatusAndComment(string orderNo, int orderStatus, string comment, string updateBy)
        {
            using (var ctx = new ExportDBEntities())
            {
                tbl_Order order = (from x in ctx.tbl_Order
                                   where x.OrderNo == orderNo
                                   select x).FirstOrDefault();

                if (order == null)
                    return false;

                order.Comment = comment;
                order.Status = orderStatus;
                order.UpdateBy = updateBy;
                order.UpdateDate = DateTime.Now;

                return ctx.SaveChanges() > 0;
            }
        }
    }
}